<?php

/**
 * @author ducnhat
 * @email ddnhat@gmail.com
 * @copyright 2014
 */

class Admin_Model_BuyLiabilities extends Zend_Db_Table{
	
	public function getDept($supplier_id = null, $date = null){
        $db = Zend_Registry::get('db');
        $db->setFetchMode(Zend_Db::FETCH_OBJ);
        
        $select = $db->select()
                    ->from(array('d' => 'buy_dept'), array('SUM(dept) AS dept'))
                    ->joinRight(array('s' => 'suppliers'), "s.id = d.supplier_id", array('name', 'id as supplier_id'));
        
        if($date != null){
            $select->where('d.date < ?', $date);
        }
        
        if($supplier_id != null){
            $select->where("d.supplier_id = ?", $supplier_id);
            $result = $db->fetchRow($select);
        }else{
            $select->group("s.id")
                    ->order("s.name");
            $result = $db->fetchAll($select);
        }
        
        return $result;
    }
    
    public function getPayment($supplier_id = null, $date = null){
    	$db = Zend_Registry::get('db');
        $db->setFetchMode(Zend_Db::FETCH_OBJ);
        
        $select = $db->select()
                    ->from(array('p' => 'buy_payment'), array('SUM(total) AS total'))
                    ->joinRight(array('s' => 'suppliers'), "s.id = p.supplier_id", array('name', 'id as supplier_id'));
        
        if($date != null){
            $select->where('p.date < ?', $date);
        }
        
        if($supplier_id != null){
            $select->where("p.supplier_id = ?", $supplier_id);
            $result = $db->fetchRow($select);
        }else{
            $select->group("s.id")
                    ->order("s.name");
            $result = $db->fetchAll($select);
        }
        
        return $result;
    }
    
    public function getOrders($supplier_id = null, $date = null){
    	$db = Zend_Registry::get('db');
        $db->setFetchMode(Zend_Db::FETCH_OBJ);
        
        $select = $db->select()
                    ->from(array('o' => 'order_buys'), array('SUM(subtotal) AS subtotal', 'SUM(totalpay) AS totalpay'))
                    ->joinRight(array('s' => 'suppliers'), "s.id = o.supplier_id", array('name', 'id as supplier_id'));
        
        if($date != null){
            $select->where('o.date < ?', $date);
        }
        
        if($supplier_id != null){
            $select->where("o.supplier_id = ?", $supplier_id);
            $result = $db->fetchRow($select);
        }else{
            $select->group("s.id")
                    ->order("s.name");
            $result = $db->fetchAll($select);
        }
        
        return $result;
    }
    
    public function getRefund($supplier_id = null, $date = null){
    	$db = Zend_Registry::get('db');
        $db->setFetchMode(Zend_Db::FETCH_OBJ);
        
        $select = $db->select()
                    ->from(array('r' => 'buy_refund'), array('SUM(total) AS total'))
                    ->joinRight(array('s' => 'suppliers'), "s.id = r.supplier_id", array('name', 'id as supplier_id'));
        
        if($date != null){
            $select->where("r.date < ?", $date);
        }
        
        if($supplier_id != null){
            $select->where("r.supplier_id = ?", $supplier_id);
            $result = $db->fetchRow($select);
        }else{
            $select->group("s.id")
                    ->order("s.name");
            $result = $db->fetchAll($select);
        }
        
        return $result;
    }
    
    public function viewQuantity(){
        $db = Zend_Registry::get('db');
        $db->setFetchMode(Zend_Db::FETCH_OBJ);
        
        $select = $db->select()
                    ->from(array('od' => 'order_buy_detail'), array('price', 'quantity', 'total'))
                    ->join(array('p' => 'product_buys'), "p.id = od.product_id", array('name'))
                    ->join(array('o' => 'order_buys'), "o.id = od.order_id", array('date'))
                    ->join(array('s' => 'suppliers'), "o.supplier_id = s.id", array('name as supplier'))
                    ->order("o.date DESC");
        
        $result = $db->fetchAll($select);
        
        return $result;
    }
}